package org.byd.dmsja.utils;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * 数据库实用类。
 **/
public class SNEDbUtils {

    /**
     * openDb - 打开数据库。
     **/
    public static Connection openDb(String dbDriver, String dbUrl, String dbUser, String dbPassword) throws SQLException {
        Connection ret = null;
        var errMsg = "";
        try {
            Class.forName(dbDriver);
            ret = DriverManager.getConnection(dbUrl, dbUser, dbPassword);
        }
        catch (Exception ex) {
            errMsg = ex.getMessage();
        }
        if (!errMsg.isEmpty()) {
            throw new SQLException(errMsg);
        }
        return ret;
    }

    /**
     * closeDb - 关闭数据库。
     **/
    public static void closeDb(Connection db) {
        try {
            if (db != null) {
                if (!db.isClosed()) {
                    db.close();
                }
            }
        }
        catch (Exception ex) {
            var dummy = "";
        }
    }

    /**
     * getSchema - 获取结构。
     **/
    public static List<CColumnSchema> getSchema(PreparedStatement ps) throws SQLException {
        List<CColumnSchema> ret = new ArrayList<>();
        try {
            var md = ps.getMetaData();
            for (var i = 1; i <= md.getColumnCount(); i++) {
                var colSchema = new CColumnSchema();
                colSchema.isAutoIncrement = md.isAutoIncrement(i);
                colSchema.isCaseSensitive = md.isCaseSensitive(i);
                colSchema.isSearchable = md.isSearchable(i);
                colSchema.isCurrency = md.isCurrency(i);
                colSchema.isNullable = md.isNullable(i);
                colSchema.isSigned = md.isSigned(i);
                colSchema.columnDisplaySize = md.getColumnDisplaySize(i);
                colSchema.columnLabel = md.getColumnLabel(i);
                colSchema.columnName = md.getColumnName(i);
                colSchema.schemaName = md.getSchemaName(i);
                colSchema.precision = md.getPrecision(i);
                colSchema.scale = md.getScale(i);
                colSchema.tableName = md.getTableName(i);
                colSchema.catalogName = md.getCatalogName(i);
                colSchema.columnType = md.getColumnType(i);
                colSchema.columnTypeName = md.getColumnTypeName(i);
                colSchema.isReadOnly = md.isReadOnly(i);
                colSchema.isWritable = md.isWritable(i);
                colSchema.isDefinitelyWritable = md.isDefinitelyWritable(i);
                colSchema.columnClassName = md.getColumnClassName(i);
                ret.add(colSchema);
            }
        }
        catch (Exception ex) {
            throw new SQLException(ex);
        }
        return ret;
    }

    /**
     * getScalar - 获取第一行第一列数据。
     **/
    public static Object getScalar(PreparedStatement ps) throws SQLException {
        Object ret;
        try (var rs = ps.executeQuery()) {
            rs.next();
            ret = rs.getObject(1);
        }
        catch (Exception ex) {
            throw new SQLException(ex);
        }
        return ret;
    }

    /**
     * getData - 获取数据。
     **/
    public static List<Map<String, Object>> getData(PreparedStatement ps) throws SQLException {
        List<Map<String, Object>> ret = new ArrayList<>();
        try (var rs = ps.executeQuery()) {
            var md = rs.getMetaData();
            while (rs.next()) {
                Map<String, Object> map = new LinkedHashMap<>();
                for (var i = 1; i <= md.getColumnCount(); i++) {
                    map.put(md.getColumnName(i), rs.getObject(i));
                }
                ret.add(map);
            }
        }
        catch (Exception ex) {
            throw new SQLException(ex);
        }
        return ret;
    }

    /**
     * getData - 获取强类型数据。
     **/
    public static <TRecord> List<TRecord> getDataStrict(PreparedStatement ps, Class<TRecord> clsT) throws SQLException {
        List<TRecord> ret = new ArrayList<>();
        try (var rs = ps.executeQuery()) {
            // 匹配字段
            var fds = clsT.getFields();
            var mds = rs.getMetaData();
            List<Map<String, Object>> mas = new ArrayList<>();
            for (var fd : fds) {
                for (var j = 1; j <= mds.getColumnCount(); j++) {
                    if (Objects.equals(mds.getColumnName(j), fd.getName())) {
                        var mapItem = new HashMap<String, Object>();
                        mapItem.put("jaField", fd);
                        mapItem.put("dbColNo", j);
                        mas.add(mapItem);
                    }
                }
            }
            // 读取数据
            while (rs.next()) {
                var rec = clsT.getDeclaredConstructor().newInstance();
                for (var ma : mas) {
                    var fd = (Field) ma.get("jaField");
                    var no = (Integer) ma.get("dbColNo");
                    fd.set(rec, rs.getObject(no));
                }
                ret.add(rec);
            }
        }
        catch (Exception ex) {
            throw new SQLException(ex);
        }
        return ret;
    }

    /**
     * getPageData - 获取分页数据。
     **/
    public static CGetPageDataParaOut getPageData(
        Connection db, CGetPageDataParaIn option) throws SQLException {
        var ret = new CGetPageDataParaOut();
        try {
            // 调用公用子过程。
            var subRet = SNEDbUtils.priGetPageData_GetData(db, option);
            SNE.shadowClone(ret, subRet);
            // 获取记录集。
            var sql = CGetPageDataSql.sqlPageData;
            Map<String, String> repMap = new LinkedHashMap<>();
            repMap.put("_tb_exp_", ret.tbExp);
            repMap.put("_where_exp_", ret.whereExp);
            repMap.put("_order_by_exp_", ret.orderByExp);
            repMap.put("_page_size_", ret.pageSize.toString());
            repMap.put("_offset_", ((Integer) (ret.pageIndex * ret.pageSize)).toString());
            sql = SNE.batchReplace(sql, repMap, true);
            try (var ps = db.prepareStatement(sql)) {
                ret.dataList = SNEDbUtils.getData(ps);
            }
            catch (Exception ex) {
                throw new SQLException(ex);
            }
        }
        catch (Exception ex) {
            throw new SQLException(ex);
        }
        // 输出结果。
        return ret;
    }

    /**
     * getPageDataStrict - 获取分页数据（强类型）。
     **/
    public static <TRecord> CGetPageDataParaOutStrict<TRecord> getPageDataStrict(
        Connection db, CGetPageDataParaIn option, Class<TRecord> clsT) throws SQLException {
        var ret = new CGetPageDataParaOutStrict<TRecord>();
        try {
            // 调用公用子过程。
            var subRet = SNEDbUtils.priGetPageData_GetData(db, option);
            SNE.shadowClone(ret, subRet);
            // 获取记录集。
            var sql = CGetPageDataSql.sqlPageData;
            Map<String, String> repMap = new LinkedHashMap<>();
            repMap.put("_tb_exp_", ret.tbExp);
            repMap.put("_where_exp_", ret.whereExp);
            repMap.put("_order_by_exp_", ret.orderByExp);
            repMap.put("_page_size_", ret.pageSize.toString());
            repMap.put("_offset_", ((Integer) (ret.pageIndex * ret.pageSize)).toString());
            sql = SNE.batchReplace(sql, repMap, true);
            try (var ps = db.prepareStatement(sql)) {
                ret.dataList = SNEDbUtils.getDataStrict(ps, clsT);
            }
            catch (Exception ex) {
                throw new SQLException(ex);
            }
        }
        catch (Exception ex) {
            throw new SQLException(ex);
        }
        // 输出结果。
        return ret;
    }

    // priGetPageData_GetData - 获取分页数据 - 内部用。
    private static CGetPageDataParaOutBase priGetPageData_GetData(
        Connection db, CGetPageDataParaIn option) throws SQLException {
        var ret = new CGetPageDataParaOutBase();
        try {
            // 规范参数。
            {
                SNE.shadowClone(ret, option);
                ret.pageIndex = ret.pageIndex <= 0 ? 0 : ret.pageIndex;
                ret.pageSize = ret.pageSize <= 1 ? 1 : ret.pageSize;
                ret.locateExp = SNE.blankElse(ret.locateExp, "");
                ret.orderByExp = SNE.blankElse(ret.orderByExp, "(select 0)");
                ret.whereExp = SNE.blankElse(ret.whereExp, "1=1");
                ret.tbExp = SNEDbUtils.trimSql(SNE.blankElse(ret.tbExp, ""));
                ret.tbExp = ret.tbExp != null && ret.tbExp.toLowerCase().startsWith("select ") ?
                    SNEDbUtils.genSqlTbExp(ret.tbExp) : ret.tbExp;
            }
            // 获取总记录数。
            {
                var sql = CGetPageDataSql.sqlTotalRecCount;
                Map<String, String> repMap = new LinkedHashMap<>();
                repMap.put("_tb_exp_", ret.tbExp);
                repMap.put("_where_exp_", ret.whereExp);
                sql = SNE.batchReplace(sql, repMap, true);
                try (var ps = db.prepareStatement(sql);
                     var rs = ps.executeQuery()
                ) {
                    rs.next();
                    ret.totalRecCount = rs.getInt(1);
                }
                catch (Exception ex) {
                    throw new SQLException(ex);
                }
            }
            // 根据tb_exp调整pageIndex
            if (!ret.locateExp.isEmpty()) {
                var dbName = db.getMetaData().getDatabaseProductName();
                var sql = "";
                if (dbName.equalsIgnoreCase("mySql")) {
                    sql = CGetPageDataSql.sqlRecNoMySql;
                }
                else {
                    sql = CGetPageDataSql.sqlRecNo;
                }
                Map<String, String> repMap = new LinkedHashMap<>();
                repMap.put("_tb_exp_", ret.tbExp);
                repMap.put("_where_exp_", ret.whereExp);
                repMap.put("_order_by_exp_", ret.orderByExp);
                repMap.put("_locate_exp_", ret.locateExp);
                sql = SNE.batchReplace(sql, repMap, true);
                try (var ps = db.prepareStatement(sql);
                     var rs = ps.executeQuery()
                ) {
                    if (rs.next()) {
                        var rowNum = rs.getInt("sne_row_num");
                        var pageNo = (rowNum / ret.pageSize) + (rowNum % ret.pageSize > 0 ? 1 : 0);
                        ret.pageIndex = pageNo - 1;
                    }
                }
                catch (Exception ex) {
                    throw new SQLException(ex);
                }
            }
            // 调整相关参数。
            {
                var pageCount = (ret.totalRecCount / ret.pageSize) +
                                ((ret.totalRecCount % ret.pageSize) > 0 ? 1 : 0);
                var pageCount1 = Math.max(pageCount, 0);
                ret.pageIndex = ret.pageIndex > pageCount1 - 1 ? pageCount1 - 1 : ret.pageIndex;
                ret.pageIndex = ret.pageIndex <= 0 ? 0 : ret.pageIndex;
            }
        }
        catch (Exception ex) {
            throw new SQLException(ex);
        }
        // 输出结果。
        return ret;
    }

    /**
     * sneCreateSqlTbExp - 安全生成表表达式。
     **/
    public static String genSqlTbExp(String sqlSelect) {
        return Objects.isNull(sqlSelect) ? null :
            "(" + trimSql(sqlSelect) + ") d";
    }

    /**
     * trimSql - 移除Sql语句首尾空格和分号。
     **/
    public static String trimSql(String sql) {
        var rgt1 = "^([\\s;]+)";
        var rgt2 = "([\\s;]+)$";
        return Objects.isNull(sql) ? null :
            sql.replaceFirst(rgt1, "").replaceFirst(rgt2, "");
    }

    /**
     * 表字段结构定义。
     */
    public static class CColumnSchema {
        public Boolean isAutoIncrement = false;
        public Boolean isCaseSensitive = false;
        public Boolean isSearchable = false;
        public Boolean isCurrency = false;
        public Integer isNullable = 0;
        public Boolean isSigned = false;
        public Integer columnDisplaySize = 0;
        public String columnLabel = "";
        public String columnName = "";
        public String schemaName = "";
        public Integer precision = 0;
        public Integer scale = 0;
        public String tableName = "";
        public String catalogName = "";
        public Integer columnType = 0;
        public String columnTypeName = "";
        public Boolean isReadOnly = false;
        public Boolean isWritable = false;
        public Boolean isDefinitelyWritable = false;
        public String columnClassName = "";
    }

    // CGetPageDataSql - 相关sql语句。
    private static class CGetPageDataSql {
        public static final String sqlTotalRecCount =
            "select count(1) as total_rec_count\n" +
            "from _tb_exp_\n" +
            "where _where_exp_";
        public static final String sqlPageData =
            "select *\n" +
            "from _tb_exp_\n" +
            "where _where_exp_\n" +
            "order by _order_by_exp_\n" +
            "limit _page_size_ offset _offset_";
        public static final String sqlRecNo =
            "select d.*\n" +
            "from (\n" +
            "         select row_number() over (order by _order_by_exp_) as sne_row_num, *\n" +
            "         from _tb_exp_ \n" +
            "     ) d\n" +
            "where _locate_exp_";
        public static final String sqlRecNoMySql =
            "select *\n" +
            "from (select d2.*\n" +
            "      from (select @row_num1 := @row_num1 + 1 as sne_row_num, x.*\n" +
            "            from (select * from _tb_exp_) x,\n" +
            "                 (select @row_num1 := 0) r\n" +
            "            where _where_exp_\n" +
            "            order by _order_by_exp_\n" +
            "           ) d1\n" +
            "      left join (select @row_num2 := @row_num2 + 1 as sne_row_num, x.*\n" +
            "                 from (select * from _tb_exp_) x,\n" +
            "                      (select @row_num2 := 0) r\n" +
            "                 where _where_exp_\n" +
            "                 order by _order_by_exp_\n" +
            "                ) d2 on d1.sne_row_num = d2.sne_row_num\n" +
            "     ) d\n" +
            "where _locate_exp_";
    }

    /**
     * GetPageDataParaIn - 获取分页数据之输入参数。
     **/
    public static class CGetPageDataParaIn {
        /**
         * 表表达式。示例： user, (select x.* from user x) d
         **/
        public String tbExp = "";
        /**
         * 定位条件表达式。不含where关键字。
         **/
        public String locateExp = "";
        /**
         * 页大小。
         **/
        public Integer pageSize = 50;
        /**
         * 页索引号。从0开始。
         **/
        public Integer pageIndex = 0;
        /**
         * 过滤条件表达式。不含where关键字。
         **/
        public String whereExp = "";
        /**
         * 排序表达式。不含order by关键字。
         **/
        public String orderByExp = "";
    }

    /**
     * CGetPageDataParaOut - 获取分页数据之输出参数。
     **/
    public static class CGetPageDataParaOut extends CGetPageDataParaOutBase {
        /**
         * 分页数据。
         **/
        public List<Map<String, Object>> dataList = new ArrayList<>();
    }

    /**
     * CGetPageDataParaOut - 获取分页数据之输出参数(强类型)。
     **/
    public static class CGetPageDataParaOutStrict<TRecord> extends CGetPageDataParaOutBase {
        /**
         * 分页数据。
         **/
        public List<TRecord> dataList = new ArrayList<>();
    }

    /**
     * CGetPageDataParaOutBase - 获取分页数据之输出参数之基本参数
     */
    public static class CGetPageDataParaOutBase {
        /**
         * 表表达式。示例： user, (select x.* from user x) d
         **/
        public String tbExp = "";
        /**
         * 定位条件表达式。不含where关键字。
         **/
        public String locateExp = "";
        /**
         * 页大小。
         **/
        public Integer pageSize = 50;
        /**
         * 页索引号。从0开始。
         **/
        public Integer pageIndex = 0;
        /**
         * 过滤条件表达式。不含where关键字。
         **/
        public String whereExp = "";
        /**
         * 排序表达式。不含order by关键字。
         **/
        public String orderByExp = "";
        /**
         * 记录总数。
         **/
        public Integer totalRecCount = 0;
    }

    // 移除SQL语句中的注释。
    public static String sqlStripComments(String sql) {
        String SQL_COMMENT_PATTERN = "/\\*(?:.|[\\n\\r])*?\\*/";
        String SQL_LINE_COMMENT_PATTERN = "--[^\\n\\r]*";
        // 移除/**/格式的注释
        Pattern sqlCommentPattern = Pattern.compile(SQL_COMMENT_PATTERN, Pattern.DOTALL);
        Matcher matcher = sqlCommentPattern.matcher(sql);
        sql = matcher.replaceAll("");

        // 移除-- 格式的注释
        Pattern sqlLineCommentPattern = Pattern.compile(SQL_LINE_COMMENT_PATTERN);
        matcher = sqlLineCommentPattern.matcher(sql);
        return matcher.replaceAll("");
    }

    // 按分号分解SQL语句。
    public static List<String> sqlSplit(String text) {
        List<String> ret = new ArrayList<>();
        if (text != null) {
            var n1 = 0;
            var n2 = 0;
            var n3 = 0;
            var s = new StringBuilder();
            for (var c : text.toCharArray()) {
                if (c == '"') {n1 = n1 + 1;}
                if (c == '\'') {n2 = n2 + 1;}
                if (c == '`') {n3 = n3 + 1;}
                if (c != ';' || (n1 % 2) != 0 || (n2 % 2) != 0 || (n3 % 2) != 0) {
                    s.append(c);
                } else {
                    ret.add(s.toString());
                    s = new StringBuilder();
                }
            }
            ret.add(s.toString());
        }
        ret = ret.stream().filter(x -> !x.isBlank()).collect(Collectors.toList());
        ret = ret.stream().map(x -> x.trim()).collect(Collectors.toList());
        return ret;
    }

}
